
package com.rx.mgt.dao;  

import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.rx.mgt.dao.base.ManagementHibernateDaoBase;
import com.rx.mgt.domain.StatisticsInfo;
import com.rx.mgt.po.UserGroupCountBo;
import com.rx.mgt.po.UsersBo;

/** 
 * ClassName:StatisticsInfoDao 
 * Function :
 * Reason   : 
 * Date     :Feb 7, 2017 9:25:47 AM 
 * @author   luolei 
 * @since    JDK 1.6
 */
@Repository
public class StatisticsInfoDao extends ManagementHibernateDaoBase<StatisticsInfo, Long>{
    @Transactional(readOnly=true)
    public List<StatisticsInfo> getStatisticsInfoCount(UsersBo usersBo){
        String hql= " from StatisticsInfo au where 1=1  ";
        if(StringUtils.isNotBlank(usersBo.getStartDate())){
            hql=hql +" and  unix_timestamp(au.date_time)>unix_timestamp(:startTime)";
        }
        if(StringUtils.isNotBlank(usersBo.getEndDate())){
            hql=hql +" and  unix_timestamp(au.date_time)<unix_timestamp(:endTime)";
        }
        if(StringUtils.isNotBlank(usersBo.getType())){
            hql=hql +" and au.type=:type";
        }
        
        hql=hql +" order by au.date_time";
        Query query = template.getSessionFactory().getCurrentSession().createQuery(hql);
        if(StringUtils.isNotBlank(usersBo.getStartDate())){
            query.setParameter("startTime", usersBo.getStartDate());
        }
        if(StringUtils.isNotBlank(usersBo.getEndDate())){
            query.setParameter("endTime", usersBo.getEndDate());
        }
        if(StringUtils.isNotBlank(usersBo.getType())){
            query.setParameter("type", usersBo.getType());
        }
        return query.list();
    }


    @Transactional(readOnly=true)
    public List<Map<String,Object>> getUserAddInfoMations(int year) {

        String strYear = String.valueOf(year);
        String sql ="select count(1) as count,left(create_time,7) as mounth from email.user_info_ext ue where ue.status ='Y'" +
                "and left(create_time,4)=? group by left(create_time,7) order by left(create_time,7)";
        Query sqlQuery =
                template.getSessionFactory().getCurrentSession().createSQLQuery(sql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);;
        sqlQuery.setString(0,strYear);

        return sqlQuery.list();

    }

    @Transactional(readOnly=true)
    public List<Map<String,Object>> queryWpVolume(int year) {
        String strYear = String.valueOf(year);
        String sql = "select sum(ifnull(uf.longsize,0)) as size,left(uf.addtime,7) as datestr,left(uf.addtime,4) from user_folder uf where uf.type=1 and left(uf.addtime,4)=? group by left(uf.addtime,4)";
        Query sqlQuery =
                template.getSessionFactory().getCurrentSession().createSQLQuery(sql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);;
        sqlQuery.setString(0,strYear);

        return sqlQuery.list();
    }
}
  